"""
Paper Manager
Handles all paper-related database operations
"""

import logging
from datetime import datetime
from typing import Dict, List, Optional, Any
import json
import uuid

from services.database_manager import DatabaseManager

logger = logging.getLogger(__name__)

class PaperManager:
    """
    Manages paper data and related operations
    """
    
    def __init__(self, db_manager: Optional[DatabaseManager] = None):
        self.db_manager = db_manager or DatabaseManager()
    
    async def create_paper(self, conn, paper_data: Dict[str, Any]) -> Dict[str, Any]:
        """Create a new paper record"""
        try:
            # Generate UUID if not provided
            paper_id = paper_data.get('id') or str(uuid.uuid4())
            
            # Prepare paper data
            paper_record = {
                'id': paper_id,
                'arxiv_id': paper_data.get('arxiv_id'),
                'title': paper_data.get('title'),
                'authors': json.dumps(paper_data.get('authors', [])),
                'abstract': paper_data.get('abstract'),
                'categories': json.dumps(paper_data.get('categories', [])),
                'published_date': paper_data.get('published_date'),
                'updated_date': paper_data.get('updated_date'),
                'pdf_url': paper_data.get('pdf_url'),
                'doi': paper_data.get('doi'),
                'journal_ref': paper_data.get('journal_ref'),
                'primary_category': paper_data.get('primary_category'),
                'metadata': json.dumps(paper_data.get('metadata', {})),
                'created_at': datetime.utcnow(),
                'updated_at': datetime.utcnow()
            }
            
            # Insert paper
            async with conn.cursor() as cur:
                columns = ', '.join(paper_record.keys())
                placeholders = ', '.join(['%s'] * len(paper_record))
                
                await cur.execute(f"""
                    INSERT INTO papers ({columns})
                    VALUES ({placeholders})
                    RETURNING *
                """, list(paper_record.values()))
                
                result = await cur.fetchone()
                return dict(result)
                
        except Exception as e:
            logger.error(f"Failed to create paper: {e}")
            raise
    
    async def get_paper_by_id(self, conn, paper_id: str) -> Optional[Dict[str, Any]]:
        """Get paper by ID"""
        try:
            async with conn.cursor() as cur:
                await cur.execute("SELECT * FROM papers WHERE id = %s", (paper_id,))
                result = await cur.fetchone()
                
                if result:
                    paper = dict(result)
                    # Parse JSON fields
                    paper['authors'] = json.loads(paper['authors']) if paper['authors'] else []
                    paper['categories'] = json.loads(paper['categories']) if paper['categories'] else []
                    paper['metadata'] = json.loads(paper['metadata']) if paper['metadata'] else {}
                    return paper
                return None
                
        except Exception as e:
            logger.error(f"Failed to get paper {paper_id}: {e}")
            return None
    
    async def get_paper_by_arxiv_id(self, conn, arxiv_id: str) -> Optional[Dict[str, Any]]:
        """Get paper by ArXiv ID"""
        try:
            async with conn.cursor() as cur:
                await cur.execute("SELECT * FROM papers WHERE arxiv_id = %s", (arxiv_id,))
                result = await cur.fetchone()
                
                if result:
                    paper = dict(result)
                    # Parse JSON fields
                    paper['authors'] = json.loads(paper['authors']) if paper['authors'] else []
                    paper['categories'] = json.loads(paper['categories']) if paper['categories'] else []
                    paper['metadata'] = json.loads(paper['metadata']) if paper['metadata'] else {}
                    return paper
                return None
                
        except Exception as e:
            logger.error(f"Failed to get paper by arxiv_id {arxiv_id}: {e}")
            return None
    
    async def search_papers(
        self,
        query: str = None,
        categories: List[str] = None,
        authors: List[str] = None,
        date_from: datetime = None,
        date_to: datetime = None,
        limit: int = 20,
        offset: int = 0
    ) -> List[Dict[str, Any]]:
        """Search papers with various filters"""
        try:
            async with self.db_manager.get_connection() as conn:
                # Build search query
                where_conditions = []
                params = []
                param_count = 1
                
                if query:
                    where_conditions.append(f"(title ILIKE ${param_count} OR abstract ILIKE ${param_count})")
                    params.append(f"%{query}%")
                    param_count += 1
                
                if categories:
                    # Search in JSON array
                    category_conditions = []
                    for category in categories:
                        category_conditions.append(f"categories @> ${param_count}")
                        params.append(json.dumps([category]))
                        param_count += 1
                    where_conditions.append(f"({' OR '.join(category_conditions)})")
                
                if authors:
                    # Search in JSON array
                    author_conditions = []
                    for author in authors:
                        author_conditions.append(f"authors::text ILIKE ${param_count}")
                        params.append(f"%{author}%")
                        param_count += 1
                    where_conditions.append(f"({' OR '.join(author_conditions)})")
                
                if date_from:
                    where_conditions.append(f"published_date >= ${param_count}")
                    params.append(date_from)
                    param_count += 1
                
                if date_to:
                    where_conditions.append(f"published_date <= ${param_count}")
                    params.append(date_to)
                    param_count += 1
                
                # Build final query
                base_query = "SELECT * FROM papers"
                if where_conditions:
                    base_query += " WHERE " + " AND ".join(where_conditions)
                
                base_query += f" ORDER BY published_date DESC LIMIT ${param_count} OFFSET ${param_count + 1}"
                params.extend([limit, offset])
                
                async with conn.cursor() as cur:
                    await cur.execute(base_query, params)
                    results = await cur.fetchall()
                    
                    papers = []
                    for row in results:
                        paper = dict(row)
                        # Parse JSON fields
                        paper['authors'] = json.loads(paper['authors']) if paper['authors'] else []
                        paper['categories'] = json.loads(paper['categories']) if paper['categories'] else []
                        paper['metadata'] = json.loads(paper['metadata']) if paper['metadata'] else {}
                        papers.append(paper)
                    
                    return papers
                    
        except Exception as e:
            logger.error(f"Paper search failed: {e}")
            return []
    
    async def get_papers_by_category(self, category: str, limit: int = 20) -> List[Dict[str, Any]]:
        """Get papers by category"""
        try:
            async with self.db_manager.get_connection() as conn:
                async with conn.cursor() as cur:
                    await cur.execute("""
                        SELECT * FROM papers 
                        WHERE categories @> %s 
                        ORDER BY published_date DESC 
                        LIMIT %s
                    """, (json.dumps([category]), limit))
                    
                    results = await cur.fetchall()
                    
                    papers = []
                    for row in results:
                        paper = dict(row)
                        paper['authors'] = json.loads(paper['authors']) if paper['authors'] else []
                        paper['categories'] = json.loads(paper['categories']) if paper['categories'] else []
                        paper['metadata'] = json.loads(paper['metadata']) if paper['metadata'] else {}
                        papers.append(paper)
                    
                    return papers
                    
        except Exception as e:
            logger.error(f"Failed to get papers by category {category}: {e}")
            return []
    
    async def get_recent_papers(self, days: int = 7, limit: int = 20) -> List[Dict[str, Any]]:
        """Get recent papers"""
        try:
            async with self.db_manager.get_connection() as conn:
                async with conn.cursor() as cur:
                    await cur.execute("""
                        SELECT * FROM papers 
                        WHERE published_date >= NOW() - INTERVAL '%s days'
                        ORDER BY published_date DESC 
                        LIMIT %s
                    """, (days, limit))
                    
                    results = await cur.fetchall()
                    
                    papers = []
                    for row in results:
                        paper = dict(row)
                        paper['authors'] = json.loads(paper['authors']) if paper['authors'] else []
                        paper['categories'] = json.loads(paper['categories']) if paper['categories'] else []
                        paper['metadata'] = json.loads(paper['metadata']) if paper['metadata'] else {}
                        papers.append(paper)
                    
                    return papers
                    
        except Exception as e:
            logger.error(f"Failed to get recent papers: {e}")
            return []
    
    async def update_paper(self, conn, paper_id: str, updates: Dict[str, Any]) -> bool:
        """Update paper information"""
        try:
            # Prepare updates
            set_clauses = []
            params = []
            param_count = 1
            
            for field, value in updates.items():
                if field in ['authors', 'categories', 'metadata']:
                    value = json.dumps(value)
                set_clauses.append(f"{field} = ${param_count}")
                params.append(value)
                param_count += 1
            
            # Add updated_at
            set_clauses.append(f"updated_at = ${param_count}")
            params.append(datetime.utcnow())
            param_count += 1
            
            # Add paper_id for WHERE clause
            params.append(paper_id)
            
            async with conn.cursor() as cur:
                await cur.execute(f"""
                    UPDATE papers 
                    SET {', '.join(set_clauses)}
                    WHERE id = ${param_count}
                """, params)
                
                return cur.rowcount > 0
                
        except Exception as e:
            logger.error(f"Failed to update paper {paper_id}: {e}")
            return False
    
    async def delete_paper(self, conn, paper_id: str) -> bool:
        """Delete paper (soft delete by marking as inactive)"""
        try:
            async with conn.cursor() as cur:
                await cur.execute("""
                    UPDATE papers 
                    SET is_active = false, updated_at = NOW()
                    WHERE id = %s
                """, (paper_id,))
                
                return cur.rowcount > 0
                
        except Exception as e:
            logger.error(f"Failed to delete paper {paper_id}: {e}")
            return False
    
    async def get_paper_statistics(self) -> Dict[str, Any]:
        """Get paper statistics"""
        try:
            async with self.db_manager.get_connection() as conn:
                async with conn.cursor() as cur:
                    stats = {}
                    
                    # Total papers
                    await cur.execute("SELECT COUNT(*) FROM papers WHERE is_active = true")
                    result = await cur.fetchone()
                    stats['total_papers'] = result['count']
                    
                    # Papers by category
                    await cur.execute("""
                        SELECT 
                            category,
                            COUNT(*) as count
                        FROM (
                            SELECT jsonb_array_elements_text(categories) as category
                            FROM papers 
                            WHERE is_active = true
                        ) cat_list
                        GROUP BY category
                        ORDER BY count DESC
                        LIMIT 20
                    """)
                    results = await cur.fetchall()
                    stats['papers_by_category'] = [dict(row) for row in results]
                    
                    # Papers by month (last 12 months)
                    await cur.execute("""
                        SELECT 
                            DATE_TRUNC('month', published_date) as month,
                            COUNT(*) as count
                        FROM papers 
                        WHERE is_active = true 
                        AND published_date >= NOW() - INTERVAL '12 months'
                        GROUP BY month
                        ORDER BY month DESC
                    """)
                    results = await cur.fetchall()
                    stats['papers_by_month'] = [dict(row) for row in results]
                    
                    # Recent activity
                    await cur.execute("""
                        SELECT COUNT(*) 
                        FROM papers 
                        WHERE is_active = true 
                        AND created_at >= NOW() - INTERVAL '24 hours'
                    """)
                    result = await cur.fetchone()
                    stats['papers_added_today'] = result['count']
                    
                    return stats
                    
        except Exception as e:
            logger.error(f"Failed to get paper statistics: {e}")
            return {}
    
    async def get_paper_with_classification(self, paper_id: str) -> Optional[Dict[str, Any]]:
        """Get paper with its AI classification"""
        try:
            async with self.db_manager.get_connection() as conn:
                async with conn.cursor() as cur:
                    await cur.execute("""
                        SELECT 
                            p.*,
                            pc.semantic_topics,
                            pc.technical_categories,
                            pc.methodologies,
                            pc.application_domains,
                            pc.confidence_scores,
                            pc.classification_metadata
                        FROM papers p
                        LEFT JOIN paper_classifications pc ON p.id = pc.paper_id
                        WHERE p.id = %s
                    """, (paper_id,))
                    
                    result = await cur.fetchone()
                    if not result:
                        return None
                    
                    paper = dict(result)
                    
                    # Parse JSON fields
                    paper['authors'] = json.loads(paper['authors']) if paper['authors'] else []
                    paper['categories'] = json.loads(paper['categories']) if paper['categories'] else []
                    paper['metadata'] = json.loads(paper['metadata']) if paper['metadata'] else {}
                    
                    # Parse classification fields
                    if paper.get('semantic_topics'):
                        paper['semantic_topics'] = json.loads(paper['semantic_topics'])
                    if paper.get('technical_categories'):
                        paper['technical_categories'] = json.loads(paper['technical_categories'])
                    if paper.get('methodologies'):
                        paper['methodologies'] = json.loads(paper['methodologies'])
                    if paper.get('application_domains'):
                        paper['application_domains'] = json.loads(paper['application_domains'])
                    if paper.get('confidence_scores'):
                        paper['confidence_scores'] = json.loads(paper['confidence_scores'])
                    if paper.get('classification_metadata'):
                        paper['classification_metadata'] = json.loads(paper['classification_metadata'])
                    
                    return paper
                    
        except Exception as e:
            logger.error(f"Failed to get paper with classification {paper_id}: {e}")
            return None
    
    async def bulk_create_papers(self, papers_data: List[Dict[str, Any]]) -> int:
        """Bulk create papers for efficiency"""
        try:
            created_count = 0
            
            async with self.db_manager.get_connection() as conn:
                async with conn.transaction():
                    for paper_data in papers_data:
                        try:
                            # Check if paper already exists
                            existing = await self.get_paper_by_arxiv_id(conn, paper_data.get('arxiv_id'))
                            if existing:
                                continue
                            
                            # Create paper
                            await self.create_paper(conn, paper_data)
                            created_count += 1
                            
                        except Exception as e:
                            logger.warning(f"Failed to create paper {paper_data.get('arxiv_id')}: {e}")
                            continue
            
            return created_count
            
        except Exception as e:
            logger.error(f"Bulk paper creation failed: {e}")
            return 0